using Microsoft.EntityFrameworkCore;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using YoYoMooc.StudentManagement.Models;

namespace YoYoMooc.StudentManagement.Services.Services
{
    public class SQLStudentRepository : IStudentRepository
    {

        private readonly AppDbContext _dbcontext;

        public SQLStudentRepository(AppDbContext dbcontext)
        {
            _dbcontext = dbcontext;
        }



        public Student Add(Student newStudent)
        {
            _dbcontext.Database.ExecuteSqlRaw("spInsertStudent {0},{1},{2},{3}",
                newStudent.Name,
                newStudent.Email,
                newStudent.PhotoPath,
                newStudent.Major ?? MajorEnum.None 
                );
            _dbcontext.SaveChanges();
            return newStudent;
        }

        public Student Delete(int id)
        {
            Student student = _dbcontext.Students.Find(id);
            if (student != null)
            {
                _dbcontext.Students.Remove(student);
                _dbcontext.SaveChanges();
            }
            return student;
        }

        public IEnumerable<Student> GetAllStudents()
        {
            return _dbcontext.Students.FromSqlRaw("select * from Students");
        }

        public Student GetStudent(int id)
        {
            return _dbcontext.Students
                  .FromSqlInterpolated($"spGetStudentById {id}")
                  .ToList().FirstOrDefault(); 
        }

        public IEnumerable<Student> Search(string searchTerm)
        {
            if (string.IsNullOrEmpty(searchTerm))
            {
                return _dbcontext.Students;
            }

            return _dbcontext.Students.Where(e => e.Name.Contains(searchTerm) ||
                                            e.Email.Contains(searchTerm));
        }

        public IEnumerable<MajorHeadCount> StudentCountByMajorEnum(MajorEnum? major)
        {
            IEnumerable<Student> query = _dbcontext.Students;
            if (major.HasValue)
            {
                query = query.Where(e => e.Major == major.Value);
            }
            return query.GroupBy(e => e.Major)
                                .Select(g => new MajorHeadCount()
                                {
                                    Major = g.Key.Value,
                                    Count = g.Count()
                                }).ToList();
        }

        public Student Update(Student updatedStudent)
        {
            var student = _dbcontext.Students.Attach(updatedStudent);
            student.State = EntityState.Modified;
            _dbcontext.SaveChanges();
            return updatedStudent;
        }
    }
}
